﻿using DotNetCommon;
using DotNetCommon.Extensions;
using System;
using System.Collections.Concurrent;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace DBUtil.Provider.MySql;

public class MySqlIdSNOGenerator : IIdSNOGenerator
{
    /// <summary>
    /// 基于数据库的Id生成控制的存储过程名称,默认为"__proc_generateid"
    /// </summary>
    public string DBCacheGeneratorIdProcedureName { get; private set; }

    /// <summary>
    /// 基于数据库的Id生成控制的表名称,默认为"__generator_id"
    /// </summary>
    public string DBCacheGeneratorIdTableName { get; private set; }

    /// <summary>
    /// 基于数据库的Id生成控制的日志表名称,默认为"__generator_err_log"
    /// </summary>
    public string DBCacheGeneratorLogTableName { get; private set; }

    /// <summary>
    /// 基于数据库的流水号生成控制的存储过程名称,默认为"__proc_generatesno"
    /// </summary>
    public string DBCacheGeneratorSNOProcedureName { get; private set; }

    /// <summary>
    /// 基于数据库的流水号生成控制的表名称,默认为"__generator_sno"
    /// </summary>
    public string DBCacheGeneratorSNOTableName { get; private set; }

    public MySqlIdSNOGenerator(string dBCacheGeneratorIdProcedureName = null
        , string dBCacheGeneratorIdTableName = null
        , string dBCacheGeneratorLogTableName = null
        , string dBCacheGeneratorSNOProcedureName = null
        , string dBCacheGeneratorSNOTableName = null)
    {
        DBCacheGeneratorIdProcedureName = dBCacheGeneratorIdProcedureName.IfNullOrEmptyUse("__proc_generateid");
        DBCacheGeneratorIdTableName = dBCacheGeneratorIdTableName.IfNullOrEmptyUse("__generator_id");
        DBCacheGeneratorLogTableName = dBCacheGeneratorLogTableName.IfNullOrEmptyUse("__generator_err_log");
        DBCacheGeneratorSNOProcedureName = dBCacheGeneratorSNOProcedureName.IfNullOrEmptyUse("__proc_generatesno");
        DBCacheGeneratorSNOTableName = dBCacheGeneratorSNOTableName.IfNullOrEmptyUse("__generator_sno");
    }

    private static readonly ConcurrentDictionary<string, bool> _initGenerators = [];
    /// <summary>
    /// 确保已经调用过<seealso cref="InitDBCacheGenerator"/>方法
    /// </summary>
    private async Task EnsureInitGenerator(DBAccess db)
    {
        if (!_initGenerators.TryGetValue(db.DBConn, out _))
        {
            await AsyncLocker.LockAsync($"EnsureInitGenerator_{db.DBConn}", async () =>
            {
                if (!_initGenerators.TryGetValue(db.DBConn, out _))
                {
                    var b = await InitDBCacheGenerator(db);
                    if (b) _initGenerators.TryAdd(db.DBConn, true);
                }
            });
        }
    }

    private async Task<bool> InitDBCacheGenerator(DBAccess db)
    {
        if (!await db.IsTableOrViewExistAsync(DBCacheGeneratorLogTableName))
        {
            #region 新建表 Settings.DBCacheGeneratorLogTableName
            await db.ExecuteSqlAsync($@"
create table {DBCacheGeneratorLogTableName}(
  id int auto_increment primary key,-- 错误序号
  err_proc  varchar(200),-- 出现错误的存储过程或 触发器的名称
  lock_str  varchar(200),-- 申请的锁资源锁
  err_num   int,-- 错误号
  err_severity  int,-- 严重性
  err_state int,-- 错误状态号  
  err_line  int,-- 导致错误的例程中的行号
  err_msg   varchar(200),-- 错误消息的完整文本
  err_time  datetime -- 错误发生时间
) engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci");
            #endregion
        }
        if (!await db.IsTableOrViewExistAsync(DBCacheGeneratorIdTableName))
        {
            #region 新建表 Settings.DBCacheGeneratorIdTableName
            await db.ExecuteSqlAsync($@"
create table {DBCacheGeneratorIdTableName}(
	 tablename varchar(50), -- 引用表名
	 colname varchar(50), -- 引用列名
	 currentid bigint,
	 primary key(tablename,colname)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci");
            #endregion
        }
        if (!await db.IsProcedureExistAsync(DBCacheGeneratorIdProcedureName))
        {
            var procName = DBCacheGeneratorIdProcedureName;
            var tableName = DBCacheGeneratorIdTableName;
            var errTableName = DBCacheGeneratorLogTableName;
            #region 新建存储过程 Settings.DBCacheGeneratorIdProcedureName
            await db.ExecuteSqlAsync($@"
                -- Version=2021-09-19
                /*
                正常情况:
                	1. 无缓存,实际表里也没匹配到
                	2. 无缓存,实际表里匹配到
                	3. 有缓存
                异常情况:
                	1. 实际表不存在等,造成查询报错
                	2. 获取锁超时
                */

                /* 调用实例
                call __proc_generateid('test','id',1)
                */
                CREATE PROCEDURE `{procName}`(in tablename2 varchar(200) charset utf8mb4 collate utf8mb4_general_ci,in colname2 varchar(200) charset utf8mb4 collate utf8mb4_general_ci,in count2 int)
                begin	
                	-- 当前id
                    DECLARE   current2 bigint;
                    DECLARE   sql2 nvarchar(1000);
                    DECLARE   lockstr nvarchar(200);
                    DECLARE   result int;
                    DECLARE   current_database varchar(200);    

                    DECLARE EXIT HANDLER FOR SQLEXCEPTION
                    BEGIN        
                		GET CURRENT DIAGNOSTICS CONDITION 1
                		 @errno = MYSQL_ERRNO,@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
                        set current_database =(select database());
                	    set lockstr=concat(current_database,'_',tablename2,'_',colname2);
                        set result = RELEASE_LOCK(lockstr);
                        rollback;
                        insert INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
                				    values('{procName}',lockstr,@errno,null,null, null ,@p2,current_timestamp());
                        SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = @p2;
                	END;

                    start TRANSACTION;
                	-- 申请锁,20秒
                    set current_database =(select database());
                	set lockstr=concat(current_database,'_',tablename2,'_',colname2);
                	set result =GET_LOCK(lockstr,20);
                	if(result = 1) then    
                		begin 
                			-- 先从id表里查找现有值
                			set current2 = (select currentid from `{tableName}` where `tablename` = tablename2 and `colname` = colname2 limit 1);
                			if (current2 is null) then
                					-- 表里尚未缓存,从实际表里查找id列的最大值
                					begin
                						set sql2=concat('select max(',colname2,') into @current2 from ',tablename2);
                						#预处理动态SQL  
                                        set @sqlcounts = sql2; 
                						prepare stmt from @sqlcounts;  
                						execute stmt;  
                						deallocate prepare stmt;  
                                        set current2 = @current2; 
                						#获取动态SQL语句返回值  
                						if (current2 is null) then
                							-- 实际表里也没有值
                							begin
                								set current2=0+count2;
                								insert into `{tableName}`(`tablename`, `colname`, `currentid`) values(tablename2, colname2, current2);
                								select current2;
                							end;
                						else
                							-- 实际表里有值
                							begin
                								set current2= current2 + count2;
                								insert into `{tableName}`(`tablename`, `colname`, `currentid`) values(tablename2, colname2, current2);
                								select current2;
                							end;
                						end if;
                					end;
                			else
                					-- 表里已经缓存
                					begin
                						set current2 = count2+current2;
                						update `{tableName}` set `currentid` = current2 where `tablename` = tablename2 and `colname` = colname2;
                						select current2;
                					end;
                			 end if;  
                		end;
                	else
                		begin
                			rollback;
                            set @p2=concat('20秒内未获取到锁,申请锁返回:',result);
                			insert INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
                				    values('{procName}',lockstr,result,null,null, null ,@p2,current_timestamp());
                			SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = @p2;
                        end;
                    end if;
                	set result= RELEASE_LOCK(lockstr);
                    commit; 	
                end");
            #endregion
        }
        if (!await db.IsTableOrViewExistAsync(DBCacheGeneratorSNOTableName))
        {
            #region 新建表(流水号生成) Settings.DBCacheGeneratorSNOTableName
            await db.ExecuteSqlAsync($@"
create table {DBCacheGeneratorSNOTableName}
(
	tablename varchar(50), -- 引用表名
	colname varchar(50), -- 引用列名
	statictext varchar(200),
	machineidstr varchar(50),
	nowstr varchar(100),
	currentno bigint,
	primary key(tablename,colname,statictext)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci");
            #endregion
        }
        if (!await db.IsProcedureExistAsync(DBCacheGeneratorSNOProcedureName))
        {
            var procName = DBCacheGeneratorSNOProcedureName;
            var tableName = DBCacheGeneratorSNOTableName;
            var errTableName = DBCacheGeneratorLogTableName;
            #region 新建存储过程(流水号生成) Settings.DBCacheGeneratorSNOProcedureName
            await db.ExecuteSqlAsync($@"
-- Version=2021-09-19
/*
正常情况:
	1. 无缓存,实际表里也没匹配到
	2. 无缓存,实际表里匹配到
	3. 有缓存,但时间戳对不上
	4. 有缓存,时间戳也对的上
异常情况:
	1. 实际表不存在等,造成查询报错
	2. 获取锁超时
*/

/* 调用实例
call {DBCacheGeneratorSNOProcedureName}('test','sno','SNO',null,'2021-09-20 00:00:00','SNO20210920%',11,1)
*/
-- 其实, 除了自增的序列号其他部分已经在程序中计算完成,这个要根据程序计算的结果求出对应的序列号
create procedure `{DBCacheGeneratorSNOProcedureName}`(
	in tablename2 varchar(50) charset utf8mb4 collate utf8mb4_general_ci,-- 实际表名(关键值)
    in colname2 varchar(50) charset utf8mb4 collate utf8mb4_general_ci,-- 实际列名(关键值)
    in statictext2 varchar(200) charset utf8mb4 collate utf8mb4_general_ci, -- 静态文本,即: 除了时间戳和序列号的其他部分(包含机器id标识) (关键值)
    in machineidstr2 varchar(50) charset utf8mb4 collate utf8mb4_general_ci,-- 机器id标识,仅用作记录
    in nowstr2 varchar(200) charset utf8mb4 collate utf8mb4_general_ci,-- 当前流水号对应的时间戳字符串(格式为: yyyy-MM-dd HH:mm:ss)
    in likestr varchar(200) charset utf8mb4 collate utf8mb4_general_ci,-- 程序中计算的除了自增序列号的其他部分,如: 'SNO20210919%', 用于无缓存时从真实表中匹配(关键值)
    in startindex int,-- 用于从实际表中查出流水号中截取序列号部分, 注意:程序传入的startindex是以0起始,而数据库中是以1起始(关键值) 
    in count2 int -- 批量生成时的数量
)
begin
	DECLARE current2 int;
	DECLARE tempno nvarchar(200);
	DECLARE sql2 nvarchar(1000);
	DECLARE templen int;
	DECLARE lockstr nvarchar(200);
	DECLARE result int;
	DECLARE currentnowstr varchar(200);
	DECLARE err_message varchar(500);
    DECLARE err_serverity varchar(50);
    DECLARE err_state varchar(50);
    DECLARE current_database varchar(200); 
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN        
		GET CURRENT DIAGNOSTICS CONDITION 1
			@errno = MYSQL_ERRNO,@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
        set current_database =(select database());
		set lockstr = concat(current_database,'_',tablename2,'_',colname2,'_',statictext2); 
        set result = RELEASE_LOCK(lockstr);
        rollback;
        insert INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
				    values('__proc_generatesno',lockstr,@errno,null,null, null ,@p2,current_timestamp());
        SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = @p2;
	END;
	start transaction;
	-- 申请锁,20秒
    set current_database =(select database());
	set lockstr=concat(current_database,'_',tablename2,'_',colname2,'_',statictext2);
	set result =GET_LOCK(lockstr,20);
	if(result = 1) then
		begin
			-- 获取到了锁
			-- 先从流水号表里查找
            set current2=(select currentno from {tableName} where tablename = tablename2 and colname = colname2 and statictext=statictext2 limit 1);
            set currentnowstr=(select nowstr from {tableName} where tablename = tablename2 and colname = colname2 and statictext=statictext2 limit 1);
			if (current2 is null) then
				-- 表里尚未缓存,从实际表里查找流水号列的最大值
				begin
					set sql2=concat('select ',colname2,' into @tempno from ',tablename2,' where ',colname2, ' like ''',likestr,''' order by ',colname2,' desc limit 1');
					#预处理动态SQL  
					set @sqlcounts = sql2; 
					prepare stmt from @sqlcounts;  
					execute stmt;  
					deallocate prepare stmt;  
					set tempno = @tempno;
					#获取动态SQL语句返回值  
					if(tempno is null) then
						-- 实际表中也找不到参照
						begin
							insert into {tableName}(tablename, colname,statictext,machineidstr,nowstr, currentno) values(tablename2, colname2,statictext2,machineidstr2,nowstr2, count2);
							select count2;
						end;
					else
						-- 实际表中找到参照
						begin
							set current2=CONVERT(substring(tempno,startindex+1),SIGNED);
							set current2=current2+count2;
							insert into {tableName}(tablename, colname,statictext,machineidstr,nowstr, currentno) values(tablename2, colname2,statictext2,machineidstr2,nowstr2, current2);
							select current2;
						end;
					end if;
				end;
			else
				-- 缓存表里有值
				begin
					-- 判断时间戳是否匹配
					if (currentnowstr=nowstr2) then
						begin
							-- 时间戳匹配
							set current2 = current2+count2;
							update {tableName} set currentno = current2 ,machineidstr=machineidstr2 where tablename = tablename2 and colname = colname2 and statictext=statictext2;
							select current2;
						end;
					else
						begin
							-- 时间戳不匹配,从新开始
							set current2=count2;
							update {tableName} set currentno = current2,nowstr=nowstr2,machineidstr=machineidstr2 where tablename = tablename2 and colname = colname2 and statictext=statictext2;
							select current2	;
						end;
					end if;
				end;
			end if;
			commit;
		end;
	else
		begin
			-- 未获取到锁
			rollback;
			set @p2=concat('20秒内未获取到锁,申请锁返回:',result);
			insert INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
					values('__proc_generatesno',lockstr,result,null,null, null ,@p2,current_timestamp());
			SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = @p2;
		end;
	end if;
    set current_database =(select database());
	set lockstr = concat(current_database,'_',tablename2,'_',colname2,'_',statictext2); 
	set result= RELEASE_LOCK(lockstr);
	commit; 
end");
            #endregion
        }
        return true;
    }

    public async Task<long> NewIdAsync(DBAccess db, string tableName, string colName, CancellationToken cancellationToken = default)
    {
        await EnsureInitGenerator(db);
        var tableObjName = db.ParseQuotedName(tableName);
        var colObjName = db.ParseQuotedName(colName);
        var id = await db.SelectScalarAsync<long>($"call {DBCacheGeneratorIdProcedureName} ({db.ProtectStringToSeg(tableObjName.Name)},{db.ProtectStringToSeg(colObjName.Name)},1)", cancellationToken);
        return id;
    }

    public async Task<long[]> NewIdsAsync(DBAccess db, string tableName, string colName, int count, CancellationToken cancellationToken = default)
    {
        if (count < 1) throw new Exception("批量生成的数量最小为1!");
        await EnsureInitGenerator(db);
        var tableObjName = db.ParseQuotedName(tableName);
        var colObjName = db.ParseQuotedName(colName);
        var id = await db.SelectScalarAsync<long>($"call {DBCacheGeneratorIdProcedureName} ({db.ProtectStringToSeg(tableObjName.Name)},{db.ProtectStringToSeg(colObjName.Name)},{count})", cancellationToken);
        var res = new long[count];
        for (long i = id - count + 1, index = 0; i <= id; i++, index++) res[index] = i;
        return res;
    }

    /// <summary>
    /// 解析格式准备流水号生成
    /// </summary>
    /// <param name="format"></param>
    /// <param name="now"></param>
    /// <param name="machineIdString"></param>
    /// <returns></returns>
    private (string likestr, DateTime snoNow, int startindex, string statictext, string machineIdString) PrePareForSNO(SerialFormat format, DateTime now, string machineIdString)
    {
        if (machineIdString.IsNullOrWhiteSpace()) machineIdString = DotNetCommon.Machine.MachineIdString;
        var (likestr, snoNow, startindex) = SerialFormat.Parse(format, now, machineIdString);
        var statictext = "";
        var chunks = format.Chunks;
        for (int i = 0, len = chunks.Count; i < len; i++)
        {
            var _chunk = chunks[i];
            if (_chunk.Type == SerialFormatChunkType.StaticText)
            {
                statictext += _chunk.FormatString;
            }
            else if (_chunk.Type == SerialFormatChunkType.MachineText)
            {
                statictext += machineIdString.PadLeft(4, '0');
            }
        }
        return (likestr, snoNow, startindex, statictext, machineIdString);
    }

    public async Task<string> NewSNOAsync(DBAccess db, string tableName, string colName, SerialFormat serialFormat, CancellationToken cancellationToken = default)
    {
        SerialFormat.ValidFormat(serialFormat);
        await EnsureInitGenerator(db);
        var tableObjName = db.ParseQuotedName(tableName);
        var colObjName = db.ParseQuotedName(colName);
        var now = DateTime.Now;
        var (likestr, snoNow, startindex, statictext, machineIdString) = PrePareForSNO(serialFormat, now, DotNetCommon.Machine.MachineIdString);
        var sql = $@" 
call {DBCacheGeneratorSNOProcedureName}(
    {db.ProtectStringToSeg(tableObjName.Name)},
    {db.ProtectStringToSeg(colObjName.Name)},
    '{statictext}',
    '{machineIdString}',
    '{snoNow.ToString(serialFormat.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.DateText).FormatString)}',
    '{likestr}',
    {startindex},
1)";
        long no = await db.SelectScalarAsync<long>(sql, cancellationToken);

        var chunk = serialFormat.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
        var sno = likestr.Substring(0, startindex);
        var s = no.ToString();
        if (s.Length > chunk.Length)
        {
            sno += s;
        }
        else
        {
            sno += s.PadLeft(chunk.Length, '0');
        }
        return sno;
    }

    public async Task<string[]> NewSNOsAsync(DBAccess db, string tableName, string colName, SerialFormat serialFormat, int count, CancellationToken cancellationToken = default)
    {
        if (count < 1) throw new Exception("批量生成的数量最小为1!");
        SerialFormat.ValidFormat(serialFormat);
        await EnsureInitGenerator(db);
        var tableObjName = db.ParseQuotedName(tableName);
        var colObjName = db.ParseQuotedName(colName);
        var now = DateTime.Now;
        var (likestr, snoNow, startindex, statictext, machineIdString) = PrePareForSNO(serialFormat, now, DotNetCommon.Machine.MachineIdString);
        var sql = $@" 
call {DBCacheGeneratorSNOProcedureName}(
    {db.ProtectStringToSeg(tableObjName.Name)},
    {db.ProtectStringToSeg(colObjName.Name)},
    '{statictext}',
    '{machineIdString}',
    '{snoNow.ToString(serialFormat.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.DateText).FormatString)}',
    '{likestr}',
    {startindex},
{count})";

        long no = long.Parse(await db.SelectScalarAsync<string>(sql, cancellationToken));
        var chunk = serialFormat.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
        var res = new string[count];
        for (long i = no - count + 1, index = 0; i <= no; i++, index++)
        {
            var sno = likestr.Substring(0, startindex);
            var s = i.ToString();
            if (s.Length > chunk.Length)
            {
                sno += s;
            }
            else
            {
                sno += s.PadLeft(chunk.Length, '0');
            }
            res[index] = sno;
        }
        return res;
    }

    public void ClearCache(DBAccess db)
    {
        db.ExecuteSql(db.Manage.DropTableIfExistSql(DBCacheGeneratorIdTableName));
        db.ExecuteSql(db.Manage.DropTableIfExistSql(DBCacheGeneratorSNOTableName));
        db.ExecuteSql(db.Manage.DropTableIfExistSql(DBCacheGeneratorLogTableName));
        db.ExecuteSql(db.Manage.DropProcedureIfExistSql(DBCacheGeneratorIdProcedureName));
        db.ExecuteSql(db.Manage.DropProcedureIfExistSql(DBCacheGeneratorSNOProcedureName));
        _initGenerators.TryRemove(db.DBConn, out _);
    }
}
